package com.trkj.dao;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.trkj.pojo.Attendance;

public interface AttendanceDAO extends JpaRepository<Attendance, Integer>,JpaSpecificationExecutor<Attendance>{
	@Query("select att from Attendance att where att.emp.empId = ?1 and att.attendanceDate = ?2")
	public Attendance findByEmpONday (Integer empid , Date date);
	@Query(nativeQuery =true ,value = "SELECT * FROM attendances a WHERE a.attendance_date = (select date_format(now(),'%Y-%m-%d')) AND a.emp_id = ?1")
	public List<Object[]> queryEmpIsCheckWorkByEmpId(Integer empid);
	@Query(nativeQuery=true,value="SELECT t.attype_name, COUNT(t.attype_name) FROM attendancetype t \r\n" + 
			" INNER JOIN attendances a ON t.attype_id = a.attype_id \r\n" + 
			" INNER JOIN emp on a.emp_id=emp.emp_id \r\n" + 
			" GROUP BY t.attype_name")
	public List<Object []> CountAttendance();
	
	@Query(nativeQuery=true,value="SELECT t.attype_name, COUNT(t.attype_name) FROM attendancetype t \r\n" + 
			" INNER JOIN attendances a ON t.attype_id = a.attype_id \r\n" + 
			" INNER JOIN emp on a.emp_id=emp.emp_id \r\n" + 
			" WHERE emp.emp_id=?1 GROUP BY t.attype_name")
	public List<Object []> CountAttendanceAnd(Integer empId);
	
	@Query(nativeQuery=true,value="SELECT dept.dept_name,COUNT(emp.emp_id) FROM dept \r\n" + 
			" INNER JOIN emp on dept.dept_id=emp.dept_id GROUP BY dept.dept_name")
	public List<Object []> countDeptemp();
	
}
